package pandora.data.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Date;


import java.util.LinkedList;
import java.util.List;


import pandora.connection.ConnectionManager;
import pandora.data.IAccountDAO;
import pandora.data.IPriceDAO;
import pandora.data.IProductDAO;
import pandora.data.IStoreDAO;

import pandora.dto.PriceDTO;
import pandora.dto.ProductDTO;
import pandora.dto.StoreDTO;

import pandora.utils.UIDGenerator;

public class JDBCPriceDAO implements IPriceDAO{

	private Connection conn;

	// DFAJ -->

	IProductDAO pdao;
	IStoreDAO sdao;
	IAccountDAO iac;

	public JDBCPriceDAO() {
		conn = ConnectionManager.getInstance().checkOut();
		pdao = new JDBCProductDAO();
		sdao = new JDBCStoreDAO();
		iac = new JDBCAccountDAO();

	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}

	
	public List<String> selectAllValidProductsBarcode(String nif) {

		String storeOID = sdao.selectStoreOIDByNIF(nif);

		PreparedStatement stmt = null;
		List<String> searchResults = new LinkedList<String>();
		ResultSet result = null;

		try {

			String sql = "SELECT * FROM Prices WHERE ( storeOID = ? AND valid = ?) ";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, storeOID);
			stmt.setBoolean(2, true);
			stmt.executeQuery();
			result = stmt.executeQuery();

			while (result.next()) {
				String oid,ret;
				oid = result.getString("productOID");
				ret = pdao.selectBarcodefProduct(oid);
				
				searchResults.add(ret);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;

	}


	
	public String selectOIDOfPrice(String nif, String barcode) {

		PreparedStatement stmt = null;
		ResultSet result = null;
		pdao = new JDBCProductDAO();
		String oidProduct = pdao.selectOIDOfProduct(barcode);
		String oidStore = sdao.selectStoreOIDByNIF(nif);
		String oid = null;
		String sql = "SELECT * FROM Prices WHERE (productOID = ? AND storeOID = ? AND valid = ?) ";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, oidStore);
			stmt.setString(2, oidProduct);
			stmt.setString(3,"1");
			result = stmt.executeQuery();

			if (result.next()) {
				oid = result.getString("OID");
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
		return oid;
	}

	public void insertPrice(String nif, String barcode, Integer price) {


		String oids = sdao.selectStoreOIDByNIF(nif);

		String oidp = pdao.selectOIDOfProduct(barcode);

		String oid = UIDGenerator.getInstance().getKey();
		
		PreparedStatement stmt = null;
		String sql = "INSERT INTO Prices ( OID, productOID, storeOID, date, price, valid) VALUES (?, ?, ?, ?, ?, ?) ";
		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, oid);
			stmt.setString(2, oidp);
			stmt.setString(3, oids);
			stmt.setLong(4, System.currentTimeMillis());
			stmt.setInt(5, price);
			stmt.setBoolean(6, true);

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
	}

	public void delete(String nif, String barcode) {
		


		String oid = this.selectOIDOfPrice(nif, barcode);
		String sql = "DELETE FROM Prices WHERE (OID = ?) ";
		PreparedStatement stmt = null;

		if (oid != null) {

			// String productOID = selectOIDOfProduct(oid);
			// String storeOID = selectOIDOfStore(oid);

			// pdao.delete(conn, productOID);
			// sdao.delete(conn, storeOID);

			try {
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, oid);
				stmt.executeUpdate();
			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				try {
					if (stmt != null)
						stmt.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void invalidPrice(String nif, String barcode) {
		

		
		String oid = this.selectOIDOfPrice(nif, barcode);
		PreparedStatement stmt = null;
		String sql = "UPDATE Prices WHERE (OID = ?) SET (valid = ?)";
		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, oid);
			stmt.setBoolean(2, false);
			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

	}


	public List<PriceDTO> selectAllPrices() {


		PreparedStatement stmt = null;
		List<PriceDTO> searchResults = new LinkedList<PriceDTO>();
		ResultSet result = null;

		try {

			String sql = "SELECT * FROM Prices";
			stmt = conn.prepareStatement(sql); // conn es una conexion valida
			stmt.executeQuery();
			result = stmt.executeQuery();

			while (result.next()) {
				PriceDTO temp = new PriceDTO();
				String poid = result.getString("productOID");
				String soid = result.getString("storeOID");
				long millis = result.getLong("date");
				Date resultdate = new Date(millis);
				temp.setLastDate(resultdate);
				temp.setPrice(result.getInt("price"));
				temp.setValid(result.getBoolean("valid"));
				
				// get Product
				temp.setProduct((ProductDTO)pdao.getProductByOID(poid));

				// get Store
				temp.setStore((StoreDTO) sdao.getStoreByOID(soid));
				
				
				searchResults.add(temp);
				
				
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;
	}

	public List<PriceDTO> selectAllCurrentPrices() {


		PreparedStatement stmt = null;
		List<PriceDTO> searchResults = new LinkedList<PriceDTO>();
		ResultSet result = null;

		try {

			String sql = "SELECT * FROM Prices WHERE (valid = ?)";
			stmt = conn.prepareStatement(sql); // conn es una conexion valida
			stmt.executeQuery();
			stmt.setBoolean(1, true);
			result = stmt.executeQuery();

			while (result.next()) {
				PriceDTO temp = new PriceDTO();
				String poid = result.getString("productOID");
				String soid = result.getString("storeOID");
				long millis = result.getLong("date");
				Date resultdate = new Date(millis);
				temp.setLastDate(resultdate);
				temp.setPrice(result.getInt("price"));
				temp.setValid(result.getBoolean("valid"));
				
				// get Product
				temp.setProduct((ProductDTO)pdao.getProductByOID(poid));

				// get Store
				temp.setStore((StoreDTO) sdao.getStoreByOID(soid));
				
				
				searchResults.add(temp);
				
				
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;
	}
	
	public List<String> selectAllValidProducts(String nif) {


		String storeOID = sdao.selectStoreOIDByNIF(nif);

		PreparedStatement stmt = null;
		List<String> searchResults = new LinkedList<String>();
		ResultSet result = null;

		try {

			String sql = "SELECT * FROM Prices WHERE ( storeOID = ? AND valid = ?) ";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, storeOID);
			stmt.setBoolean(2, true);
			stmt.executeQuery();
			result = stmt.executeQuery();

			while (result.next()) {
				String temp;
				temp = result.getString("productOID");
				searchResults.add(temp);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;

	}

	// <-- DFAJ
	
	//--> AVC
	
	public PriceDTO selectPrice(String nif, String barcode) {
		

		PreparedStatement stmt = null;
		ResultSet result = null;
		pdao = new JDBCProductDAO();
		String oidProduct = pdao.selectOIDOfProduct(barcode);
		String oidStore = sdao.selectStoreOIDByNIF(nif);
		PriceDTO oid = null;
		String sql = "SELECT * FROM Prices WHERE (productOID = ? AND storeOID = ? AND valid = ?) ";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, oidStore);
			stmt.setString(2, oidProduct);
			stmt.setBoolean(3,true);
			result = stmt.executeQuery();

			if (result.next()) {
				PriceDTO temp = new PriceDTO();
				long millis = result.getLong("date");
				Date resultdate = new Date(millis);
				temp.setLastDate(resultdate);
				temp.setPrice(result.getInt("price"));
				temp.setValid(result.getBoolean("valid"));
				oid = temp;
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {

			
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
		return oid;
	}	
	
	public PriceDTO selectPriceStoreProduct(StoreDTO s, ProductDTO p) {
		

		
		PreparedStatement stmt = null;
		ResultSet result = null;
		String barcode = p.getBarcode();
		String nif = s.getStoreNIF();
		String oid = selectOIDOfPrice(nif,barcode);
		PriceDTO price= new PriceDTO();
		String sql = "SELECT * FROM Prices WHERE (OID = ?) ";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, oid);
			result = stmt.executeQuery();

			if (result.next()) {
				price.setProduct(p);
				price.setStore(s);
				Long millis = new Long(result.getString("date"));
				Date resultdate = new Date(millis);
				price.setLastDate(resultdate);
				price.setPrice(new Integer(result.getString("price")));
				boolean val = false;
				if(result.getString("valid").equals("1"))
					val=true;
				price.setValid(val);
				
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			

			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
		return price;
	}
	
	
	
	
	
	
	
	//--->MRS
	
	

	public List<PriceDTO> selectAllPricesBetween(String snif, Date d1, Date d2) {
		// TODO 
		return null;
	}

	public List<PriceDTO> selectAllPrices(String barcode){
		

		String oidProduct = pdao.selectOIDOfProduct(barcode);
		PreparedStatement stmt = null;
		List<PriceDTO> searchResults = new LinkedList<PriceDTO>();
		ResultSet result = null;

		try {

			String sql = "SELECT * FROM Prices P WHERE P.productOID = ? AND P.valid = ?";
			stmt = conn.prepareStatement(sql); 
			stmt.executeQuery();
			result = stmt.executeQuery();
			
			stmt.setString(1, oidProduct);
			stmt.setBoolean(2,true);

			while (result.next()) {
				PriceDTO temp = new PriceDTO();
				long millis = result.getLong("date");
				Date resultdate = new Date(millis);
				temp.setLastDate(resultdate);
				temp.setPrice(result.getInt("price"));
				temp.setValid(result.getBoolean("valid"));
				searchResults.add(temp);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;
	}
	
	//dar precios del producto en esta tienda
	public List<PriceDTO> selectAllStoresPricesBetween(Date d1,Date d2, StoreDTO st, ProductDTO p) {
		// TODO 
		List<PriceDTO> lis = new ArrayList<PriceDTO>();
		
		
		PreparedStatement stmt = null;
		ResultSet result = null;
		
		Long time1 = new Long(0);
		Long time2 = new Long(0);
		

		
		String sql = "SELECT Pr.* FROM Prices Pr, Stores S, Products P WHERE Pr.productOID = P.OID AND Pr.storeOID = S.OID AND S.NIF= ? AND P.barcode = ? AND S.OID = ? AND Pr.date BETWEEN ? AND ?";
		
		try{
			time1 = d1.getTime();
            time2 = d2.getTime();
            stmt = conn.prepareStatement(sql); 

            stmt.setString(1, st.getStoreNIF());
            stmt.setString(2, p.getBarcode());
			stmt.setString(3, time1.toString());
			stmt.setString(4, time2.toString());
			
			result = stmt.executeQuery();
			
			while(result.next()){
				PriceDTO pre = new PriceDTO();
				
				String va = result.getString("valid");
				
				long millis = result.getLong("date");
				Date resultdate = new Date(millis);
				
				pre.setLastDate(resultdate);
				pre.setPrice(result.getInt("price"));
				pre.setProduct(p);
				pre.setStore(st);
				pre.setValid(new Boolean(va));
					
				lis.add(pre);
				}
			
		}catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} 
			catch (SQLException e) {}
		}
		return lis;
	}

	
	//metodo de todas las tiendas y un producto
	public List<StoreDTO> selectPriceByStoreProduct(ProductDTO pr) {
		// TODO Auto-generated method stub

		List<StoreDTO> lis = new ArrayList<StoreDTO>();
		
		
		PreparedStatement stmt = null;
		ResultSet result = null;
		
		String sql = "SELECT S.* FROM Products P, Stores S, Prices R WHERE P.OID = R.productOID AND P.OID = ? AND S.OID = R.storeOID";
		
		try{
			
            stmt = conn.prepareStatement(sql); 
			result = stmt.executeQuery();
			
				while(result.next()){
					StoreDTO prod = new StoreDTO();
			
					stmt.setString(1, pdao.selectOIDOfProduct(pr.getBarcode()));
				
					prod.setAccount(iac.selectAccountByOID(result.getString("accountOID")));
					prod.setStoreNIF(result.getString("NIF"));
					prod.setStoreName(result.getString("storeName"));
					prod.setStoreSite(result.getString("storeSite"));
					prod.setStoreDetails(result.getString("storeDetails"));
					prod.setStoreAddress(result.getString("storeAddress"));
					
					lis.add(prod);
				}
		
		}catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} 
			catch (SQLException e) {}
		}
		return lis;
		
		
	}

	public List<PriceDTO> selectAllStoresPricesBetween(StoreDTO ap, Date d1,	Date d2) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<PriceDTO> selectPriceByStoreProduct(StoreDTO st, ProductDTO pr) {
		// TODO Auto-generated method stub
		return null;
	}

	

	
	


	
	
	//MRS<----
	

}
